﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using Npgsql;
using NpgsqlTypes;

namespace appLibrary
{
    public class ViewQueries
    {


        public void getData(string [] strFields,string strNameTable, string strNameFieldCond1,string strNameFieldCond2,
            string strNameFieldCond3, string strValueCond3,string strUser, ref string strCod, ref string strDesc)
        {
            
            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                DataSet dt = new  DataSet();
                string sql = "";
                if(strNameFieldCond3!=null)
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond3 + " in(" + strValueCond3+")" +
                            "ORDER BY 1";
                else
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable+ " ORDER BY 1";

                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();
                
                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dt, strNameTable);

                table = dt.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: "+strNameTable+" satisfactoriamente");

                FrmViewQueries frmViewQueries = new FrmViewQueries();
                frmViewQueries.Text = frmViewQueries.Text + " - " + strNameTable;
                frmViewQueries.dtTable = table;
                frmViewQueries.strNameTable = strNameTable;
                frmViewQueries.strFields = strFieldsQueries;
                frmViewQueries.strFieldCondicion1 = strNameFieldCond1;
                frmViewQueries.strFieldCondicion2 = strNameFieldCond2;
                frmViewQueries.strFieldCondicion3 = strNameFieldCond3;
                frmViewQueries.strValueCond3= strValueCond3;
                frmViewQueries.ShowDialog();
                frmViewQueries.BringToFront();
                strCod = frmViewQueries.codeFind;
                strDesc = frmViewQueries.descFind;

                Log.eLog(strUser, "Valores capturados: " + strCod + "," + strDesc);
                
                

            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strNameTable + e);
                
            }
            finally
            {

                // conn = null;
            }

            
        }

        public void getData(string[] strFields, string strNameTable, string strNameFieldCond1, string strNameFieldCond2,
         string strNameFieldCond3, string strValueCond3, string strUser, ref string strCod, ref string strDesc, ref DataGridViewRow row)
        {

            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                DataSet dts = new DataSet();
                string sql = "";
                if (strNameFieldCond3 != null)
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond3 + " in(" + strValueCond3 + ")" +
                            "ORDER BY 1";
                else
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " ORDER BY 1";

                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();

                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dts, strNameTable);

                table = dts.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: " + strNameTable + " satisfactoriamente");

                FrmViewQueries frmViewQueries = new FrmViewQueries();
                frmViewQueries.Text = frmViewQueries.Text + " - " + strNameTable;
                frmViewQueries.dtTable = table;
                frmViewQueries.strNameTable = strNameTable;
                frmViewQueries.strFields = strFieldsQueries;
                frmViewQueries.strFieldCondicion1 = strNameFieldCond1;
                frmViewQueries.strFieldCondicion2 = strNameFieldCond2;
                frmViewQueries.strFieldCondicion3 = strNameFieldCond3;
                frmViewQueries.strValueCond3 = strValueCond3;
                frmViewQueries.ShowDialog();
                frmViewQueries.BringToFront();
                strCod = frmViewQueries.codeFind;
                strDesc = frmViewQueries.descFind;
                row = frmViewQueries.row;

                Log.eLog(strUser, "Valores capturados: " + strCod + "," + strDesc);



            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strNameTable + e);

            }
            finally
            {

                // conn = null;
            }


        }


        public void getData(string[] strFields,int[]intColumnsHide, string strNameTable, string strAliasTable, string strNameFieldCond1, string strNameFieldCond2,
        string strNameFieldCond3, string strValueCond3, string strUser, ref string strCod, ref string strDesc, ref DataGridViewRow row)
        {

            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                DataSet dts = new DataSet();
                string sql = "";
                if (strNameFieldCond3 != null)
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond3 + " in(" + strValueCond3 + ")" +
                            "ORDER BY 1";
                else
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " ORDER BY 1";

                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();

                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dts, strNameTable);

                table = dts.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: " + strNameTable + " satisfactoriamente");

                FrmViewQueries frmViewQueries = new FrmViewQueries();
                frmViewQueries.Text = frmViewQueries.Text + " - " +(strAliasTable!=null?strAliasTable: strNameTable);
                frmViewQueries.dtTable = table;
                frmViewQueries.strNameTable = strNameTable;
                frmViewQueries.strFields = strFieldsQueries;
                frmViewQueries.strFieldCondicion1 = strNameFieldCond1;
                frmViewQueries.strFieldCondicion2 = strNameFieldCond2;
                frmViewQueries.strFieldCondicion3 = strNameFieldCond3;
                frmViewQueries.strValueCond3 = strValueCond3;
                frmViewQueries.intColumnsHide = intColumnsHide;
                frmViewQueries.ShowDialog();
                frmViewQueries.BringToFront();
                strCod = frmViewQueries.codeFind;
                strDesc = frmViewQueries.descFind;
                row = frmViewQueries.row;

                Log.eLog(strUser, "Valores capturados: " + strCod + "," + strDesc);



            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strNameTable + e);

            }
            finally
            {

                // conn = null;
            }


        }

        //Metodo sobrecargado que limita el query con el numero de registro
        public void getData(string[] strFields, int[] intColumnsHide, string strNameTable, 
            string strAliasTable, string strNameFieldCond1, string strNameFieldCond2,
            string strNameFieldCond3, string strValueCond3, string strUser,ref DataGridViewRow row,int numReg )
        {

            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                DataSet dts = new DataSet();
                string sql = "";
                if (strNameFieldCond3 != null)
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond3 + " in(" + strValueCond3 + ")" +
                            "ORDER BY 1 LIMIT " + numReg;
                else
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " ORDER BY 1 LIMIT "+ numReg;

                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();

                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dts, strNameTable);

                table = dts.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: " + strNameTable + " satisfactoriamente");

                FrmViewQueries frmViewQueries = new FrmViewQueries();
                frmViewQueries.Text = frmViewQueries.Text + " - " + (strAliasTable != null ? strAliasTable : strNameTable);
                frmViewQueries.dtTable = table;
                frmViewQueries.strNameTable = strNameTable;
                frmViewQueries.strFields = strFieldsQueries;
                frmViewQueries.strFieldCondicion1 = strNameFieldCond1;
                frmViewQueries.strFieldCondicion2 = strNameFieldCond2;
                frmViewQueries.strFieldCondicion3 = strNameFieldCond3;
                frmViewQueries.strValueCond3 = strValueCond3;
                frmViewQueries.intColumnsHide = intColumnsHide;
                frmViewQueries.ShowDialog();
                frmViewQueries.BringToFront();
                //strCod = frmViewQueries.codeFind;
                //strDesc = frmViewQueries.descFind;
                row = frmViewQueries.row;

                //Log.eLog(strUser, "Valores capturados: " + strCod + "," + strDesc);



            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strAliasTable +":"+ e.ToString());

            }
            finally
            {

                // conn = null;
            }


        }


        public void getData(string[] strFields, int[] intColumnsHide, string strNameTable, string strAliasTable, string strNameFieldCond1, string strNameFieldCond2,
        string strNameFieldCond3, string strValueCond3, string strUser, ref string strCod, ref string strDesc)
        {

            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                DataSet dts = new DataSet();
                string sql = "";
                if (strNameFieldCond3 != null)
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond3 + " in(" + strValueCond3 + ")" +
                            "ORDER BY 1";
                else
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " ORDER BY 1";

                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();

                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dts, strNameTable);

                table = dts.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: " + strNameTable + " satisfactoriamente");

                FrmViewQueries frmViewQueries = new FrmViewQueries();
                frmViewQueries.Text = frmViewQueries.Text + " - " + (strAliasTable != null ? strAliasTable : strNameTable);
                frmViewQueries.dtTable = table;
                frmViewQueries.strNameTable = strNameTable;
                frmViewQueries.strFields = strFieldsQueries;
                frmViewQueries.strFieldCondicion1 = strNameFieldCond1;
                frmViewQueries.strFieldCondicion2 = strNameFieldCond2;
                frmViewQueries.strFieldCondicion3 = strNameFieldCond3;
                frmViewQueries.strValueCond3 = strValueCond3;
                frmViewQueries.intColumnsHide = intColumnsHide;
                frmViewQueries.ShowDialog();
                strCod = frmViewQueries.codeFind;
                strDesc = frmViewQueries.descFind;
                //row = frmViewQueries.row;

                Log.eLog(strUser, "Valores capturados: " + strCod + "," + strDesc);



            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strNameTable + e);

            }
            finally
            {

                // conn = null;
            }


        }

        
        public void getDataWithoutForm(string[] strFields, string strNameTable, string strNameFieldCond1,
           string strNameFieldCond2, string strValueCond1, string strUser, string strValueCond2, ref string strDesc)
        {

            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                DataSet dt = new DataSet();
                string sql = "";
                if (strNameFieldCond2 != null)
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond1 + " in(" + strValueCond1 + ")  AND " +
                        strNameFieldCond2 + "=" + (Util.isnumeric(strValueCond2) ? strValueCond2 : "'" + strValueCond2 + "'");
                else
                    sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond1 + " in(" + strValueCond1 + ") ";
               
                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();

                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dt, strNameTable);

                table = dt.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: " + strNameTable + " satisfactoriamente");

                if (table != null && table.Rows.Count > 0)
                {
                    strDesc = table.Rows[0][1].ToString();



                    Log.eLog(strUser, "Valores capturados: " + table.Rows[0][0].ToString() + "," + strDesc);

                }

            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strNameTable + e);

            }
            finally
            {

                // conn = null;
            }


        }



        public void getDataWithoutForm(string[] strFields, string strNameTable, string strNameFieldCond1,
         string strNameFieldCond2, string strValueCond1, string strUser, string strValueCond2, ref string strDesc,ref DataSet dts)
        {

            DataTable table = new DataTable();
            //table.Columns.Add("Código", typeof(string));
            //table.Columns.Add("Descripción", typeof(string));
            string strFieldsQueries = "";
            for (int i = 0; i < strFields.Length; i++)
            {
                if (i == strFields.Length - 1)
                    strFieldsQueries = strFieldsQueries + strFields[i];
                else
                    strFieldsQueries = strFieldsQueries + strFields[i] + ",";
            }

            Log.eLog(strUser, "Campos:" + strFieldsQueries);

            try
            {
                //DataSet dt = new DataSet();
                string sql = "";
                sql = "SELECT " + strFieldsQueries + " FROM " + strNameTable + " WHERE " + strNameFieldCond1 + " in(" + strValueCond1 + ")  AND " +
                        strNameFieldCond2 + "=" + (Util.isnumeric(strValueCond2) ? strValueCond2 : "'" + strValueCond2 + "'");

                Log.eLog(strUser, "Queries:" + sql);
                ConnectionDB conn = new ConnectionDB();

                NpgsqlDataAdapter dtAdap = new NpgsqlDataAdapter(sql, conn.Connecction());

                dtAdap.Fill(dts, strNameTable);

                table = dts.Tables[0];
                Log.eLog(strUser, "Se realizó la consulta a la tabla: " + strNameTable + " satisfactoriamente");

                if (table != null && table.Rows.Count > 0)
                {
                    strDesc = table.Rows[0][1].ToString();



                    Log.eLog(strUser, "Valores capturados: " + table.Rows[0][0].ToString() + "," + strDesc);

                }

            }
            catch (Exception e)
            {
                Log.eLog(strUser, "ERROR: Se obtuvieron errores al realizar  la consulta a la tabla: " + strNameTable + e);

            }
            finally
            {

                // conn = null;
            }


        }


        public void getSP(string strNameStoreProcedure, NpgsqlConnection conn, params object[] paramValue)
        {
            //ConnectionDB connectionDB = new ConnectionDB();
            // NpgsqlConnection conn=connectionDB.Connecction();
            // conn.Open();
            int i = 0;
            try
            {
                NpgsqlCommand cmd = new NpgsqlCommand(strNameStoreProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //Le pasamos los parametros, aquí los nombres de los parámetros no importa mucho ya que si
                //no fijamos la función no tienen nombre solo el tipo de dato.
                //NpgsqlCommandBuilder cmb=new NpgsqlCommandBuilder();
                //cmb.
                NpgsqlCommandBuilder.DeriveParameters(cmd);

                foreach (NpgsqlParameter par in cmd.Parameters)
                {
                    //MessageBox.Show(par.ParameterName);
                    //MessageBox.Show(par.ParameterName);
                    //Log.eLog("uknow", cmd.Parameters[i].GetType().ToString());
                    if (paramValue[i] != null && Util.isString(paramValue[i].ToString()))
                    {
                        cmd.Parameters[par.ParameterName].Value = Convert.ToString(paramValue[i]);
                        i++;
                        continue;
                    }
                    if (paramValue[i] != null && Util.isArrayInteger(paramValue[i]))
                    {
                        cmd.Parameters[par.ParameterName].Value = (int[])paramValue[i];
                        i++;
                        continue;
                    }
                    if (paramValue[i] != null && Util.isArrayBytes(paramValue[i]))
                    {
                        cmd.Parameters[par.ParameterName].Value = (byte[])paramValue[i];
                        i++;
                        continue;
                    }
                   
                    if (paramValue[i] != null && Util.isnumeric(paramValue[i].ToString()))
                    {
                        cmd.Parameters[par.ParameterName].Value = Convert.ToInt32(paramValue[i].ToString());
                        i++;
                        continue;
                    }
                    if (paramValue[i] != null && Util.isDateTime(paramValue[i].ToString()))
                    {
                        cmd.Parameters[par.ParameterName].Value = Convert.ToDateTime(paramValue[i]);
                        i++;
                        continue;
                    }
                    i++;
                }

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Log.eLog("unkNow", "Valor  que genera el  error:" +ex.ToString());
                throw new Exception(ex.Message);
            }


        }


        public void getSPMantSingle(string strNameStoreProcedure, params object[] paramValue)
        {
            ConnectionDB connectionDB = new ConnectionDB();
            NpgsqlConnection conn=connectionDB.Connecction();
            conn.Open();
            NpgsqlCommand cmd = new NpgsqlCommand(strNameStoreProcedure, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            //Le pasamos los parametros, aquí los nombres de los parámetros no importa mucho ya que si
            //no fijamos la función no tienen nombre solo el tipo de dato.
            //NpgsqlCommandBuilder cmb=new NpgsqlCommandBuilder();
            //cmb.
            NpgsqlCommandBuilder.DeriveParameters(cmd);
            int i = 0;
            foreach (NpgsqlParameter par in cmd.Parameters)
            {
                //MessageBox.Show(par.ParameterName);
                //Log.eLog("uknow", cmd.Parameters[i].GetType().ToString());
                if (paramValue[i] != null && Util.isArrayString(paramValue[i]))
                {
                    cmd.Parameters[par.ParameterName].Value = (string[])paramValue[i];
                    i++;
                    continue;
                }

                if (paramValue[i] != null && Util.isArrayBytes(paramValue[i]))
                {
                    cmd.Parameters[par.ParameterName].Value = (byte[])paramValue[i];
                    i++;
                    continue;
                }

                if (paramValue[i] != null && Util.isString(paramValue[i].ToString()))
                {
                    cmd.Parameters[par.ParameterName].Value = Convert.ToString(paramValue[i]);
                    i++;
                    continue;
                }
                if (paramValue[i] != null && Util.isnumeric(paramValue[i].ToString()))
                {
                    cmd.Parameters[par.ParameterName].Value = Convert.ToInt32(paramValue[i].ToString());
                    i++;
                    continue;
                }
                if (paramValue[i] != null && Util.isDateTime(paramValue[i].ToString()))
                {
                    cmd.Parameters[par.ParameterName].Value = Convert.ToDateTime(paramValue[i]);
                    i++;
                    continue;
                }
                               

                i++;
            }

            cmd.ExecuteNonQuery();

            conn.Close();

        }

        public DataSet  getSPQueries(string strNameStoreProcedure, params object[] paramValue)
        {
            ConnectionDB connectionDB = new ConnectionDB();
             NpgsqlConnection conn=connectionDB.Connecction();
            // Este parametro se asigna exclusivamente en una consulta
             //NpgsqlParameter objParametro = new NpgsqlParameter(":refcursor",NpgsqlDbType.Refcursor);
             //objParametro.Direction = ParameterDirection.InputOutput;
             NpgsqlDataAdapter dap = new NpgsqlDataAdapter();
             DataSet ds = null;
             try
             {
                 conn.Open();
                 NpgsqlCommand cmd = new NpgsqlCommand(strNameStoreProcedure, conn);
                 cmd.CommandType = CommandType.StoredProcedure;
                 //cmd.Parameters.Add(objParametro);
                 //Le pasamos los parametros, aquí los nombres de los parámetros no importa mucho ya que si
                 //no fijamos la función no tienen nombre solo el tipo de dato.
                 //NpgsqlCommandBuilder cmb=new NpgsqlCommandBuilder();
                 //cmb.
                 NpgsqlCommandBuilder.DeriveParameters(cmd);
                 int i = 0;
                 if(paramValue!=null){
                     foreach (NpgsqlParameter par in cmd.Parameters)
                     {
                         //MessageBox.Show(par.ParameterName);

                         if (paramValue[i] != null && Util.isArrayString(paramValue[i]))
                         {
                             cmd.Parameters[par.ParameterName].Value = (string[])paramValue[i];
                             i++;
                             continue;
                         }

                         if (paramValue[i] != null && Util.isArrayInteger(paramValue[i]))
                         {
                             cmd.Parameters[par.ParameterName].Value =(int[]) paramValue[i];
                             i++;
                             continue;
                         }

                         if (paramValue[i] != null && Util.isString(paramValue[i]))
                         {
                             cmd.Parameters[par.ParameterName].Value = Convert.ToString(paramValue[i]);
                             i++;
                             continue; 
                         }
                         if (paramValue[i] != null && Util.isnumeric(paramValue[i].ToString()))
                         {
                             cmd.Parameters[par.ParameterName].Value = Convert.ToInt32(paramValue[i].ToString());
                             i++;
                             continue;
                         }
                         if (paramValue[i] != null && Util.isDateTime(paramValue[i].ToString()))
                         {
                             cmd.Parameters[par.ParameterName].Value = Convert.ToDateTime(paramValue[i]);
                             i++;
                             continue;
                         }
                         i++;
                     }
                 }
                 dap.SelectCommand=cmd;
                 //Es obligado abrir la conexión y usar una transacción ya que el uso de refcursores(cursores)
                 //en PostgreSQL se puede hacer uso solo en  transacciones.

                 NpgsqlTransaction t = conn.BeginTransaction();
                 ds = new DataSet();
                 dap.Fill(ds, "datos");
                 t.Commit();
             }
             catch (Exception e)
             {
                 Log.eLog("Uknown", "(ERROR): "+e.ToString());
                throw new Exception(e.Message);
             }

             finally
             {
                 dap.Dispose();
                 if(conn!=null)
                    conn.Close();
             }
             return ds;

        }


        public void getSPQueries2(string strNameStoreProcedure, ref DataSet dts,string strNameTable, params object[] paramValue)
        {
            ConnectionDB connectionDB = new ConnectionDB();
            NpgsqlConnection conn = connectionDB.Connecction();
            // Este parametro se asigna exclusivamente en una consulta
            //NpgsqlParameter objParametro = new NpgsqlParameter(":refcursor",NpgsqlDbType.Refcursor);
            //objParametro.Direction = ParameterDirection.InputOutput;
            NpgsqlDataAdapter dap = new NpgsqlDataAdapter();
            //DataSet ds = null;
            try
            {
                conn.Open();
                NpgsqlCommand cmd = new NpgsqlCommand(strNameStoreProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.Add(objParametro);
                //Le pasamos los parametros, aquí los nombres de los parámetros no importa mucho ya que si
                //no fijamos la función no tienen nombre solo el tipo de dato.
                //NpgsqlCommandBuilder cmb=new NpgsqlCommandBuilder();
                //cmb.
                NpgsqlCommandBuilder.DeriveParameters(cmd);
                int i = 0;
                if (paramValue != null)
                {
                    foreach (NpgsqlParameter par in cmd.Parameters)
                    {
                        //MessageBox.Show(par.ParameterName);

                        if (paramValue[i] != null && Util.isString(paramValue[i].ToString()))
                        {
                            cmd.Parameters[par.ParameterName].Value = Convert.ToString(paramValue[i]);
                            i++;
                            continue;
                        }
                        if (paramValue[i] != null && Util.isnumeric(paramValue[i].ToString()))
                        {
                            cmd.Parameters[par.ParameterName].Value = Convert.ToInt32(paramValue[i].ToString());
                            i++;
                            continue;
                        }
                        if (paramValue[i] != null && Util.isDateTime(paramValue[i].ToString()))
                        {
                            cmd.Parameters[par.ParameterName].Value = Convert.ToDateTime(paramValue[i]);
                            i++;
                            continue;
                        }
                        i++;
                    }
                }
                dap.SelectCommand = cmd;
                //Es obligado abrir la conexión y usar una transacción ya que el uso de refcursores(cursores)
                //en PostgreSQL se puede hacer uso solo en  transacciones.

                NpgsqlTransaction t = conn.BeginTransaction();
                dap.Fill(dts, strNameTable);
                t.Commit();
            }
            catch (Exception e)
            {
                Log.eLog("Uknown", "(ERROR): " + e.ToString());
                throw new Exception(e.Message);
            }

            finally
            {
                dap.Dispose();
                if (conn != null)
                    conn.Close();
            }
            

        }


        public string encriptMD5(string strPwd)
        {
            ConnectionDB connDB = new ConnectionDB();
            NpgsqlConnection connection = connDB.Connecction();
            connection.Open();

            NpgsqlDataAdapter dap = new NpgsqlDataAdapter("Select md5('" + strPwd + "')", connDB.Connecction());

            DataSet dts=new DataSet();
            dap.Fill(dts, "test");
            if (dts != null && dts.Tables != null && dts.Tables[0].Rows.Count > 0)
                return dts.Tables[0].Rows[0][0].ToString();
            else
                return "";

        }



        public int getSequence(string strNameSequence, NpgsqlConnection conn)
        {

            NpgsqlCommand command = conn.CreateCommand();
            NpgsqlDataReader Reader;
            command.CommandText = "SELECT  last_value FROM " + strNameSequence;
            Reader = command.ExecuteReader();
            int value = 0;
            while (Reader.Read())
            {
                value = Convert.ToInt32(Reader.GetValue(0).ToString());

            }

            return value;



        }


        public DataTable executeQueriesbyFilters(string strSQL, string strUser, string strNameTable)
        {
            ConnectionDB connDB = new ConnectionDB();
            NpgsqlConnection connection = null;
            try
            {
                
                connection = connDB.Connecction();
                connection.Open();

                NpgsqlDataAdapter dap = new NpgsqlDataAdapter(strSQL, connDB.Connecction());

                DataSet dts = new DataSet();
                dap.Fill(dts, strNameTable);
                if (dts != null && dts.Tables != null && dts.Tables[0].Rows.Count > 0)
                    return dts.Tables[0];
                else
                    return null;
            }
            catch (Exception e)
            {
                Log.eLog(strUser, "(ERROR): se obtuvo errores la intentar consultar la tabla:" + e.ToString());
                throw;
            }
            finally
            {
                if (connection!=null)
                    connection.Close();
            }

        }


        public object getSPQueriesEscalar(string strNameStoreProcedure, params object[] paramValue)
        {
            ConnectionDB connectionDB = new ConnectionDB();
            NpgsqlConnection conn = connectionDB.Connecction();
            // Este parametro se asigna exclusivamente en una consulta
            //NpgsqlParameter objParametro = new NpgsqlParameter(":refcursor",NpgsqlDbType.Refcursor);
            //objParametro.Direction = ParameterDirection.InputOutput;
            
            try
            {
                conn.Open();
                NpgsqlCommand cmd = new NpgsqlCommand(strNameStoreProcedure, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                //cmd.Parameters.Add(objParametro);
                //Le pasamos los parametros, aquí los nombres de los parámetros no importa mucho ya que si
                //no fijamos la función no tienen nombre solo el tipo de dato.
                //NpgsqlCommandBuilder cmb=new NpgsqlCommandBuilder();
                //cmb.
                NpgsqlCommandBuilder.DeriveParameters(cmd);
                int i = 0;
                if (paramValue != null)
                {
                    foreach (NpgsqlParameter par in cmd.Parameters)
                    {
                        //MessageBox.Show(par.ParameterName);

                        if (paramValue[i] != null && Util.isString(paramValue[i].ToString()))
                        {
                            cmd.Parameters[par.ParameterName].Value = Convert.ToString(paramValue[i]);
                            i++;
                            continue;
                        }
                        if (paramValue[i] != null && Util.isnumeric(paramValue[i].ToString()))
                        {
                            cmd.Parameters[par.ParameterName].Value = Convert.ToInt32(paramValue[i].ToString());
                            i++;
                            continue;
                        }
                        if (paramValue[i] != null && Util.isDateTime(paramValue[i].ToString()))
                        {
                            cmd.Parameters[par.ParameterName].Value = Convert.ToDateTime(paramValue[i]);
                            i++;
                            continue;
                        }
                        i++;
                    }
                }
                
                return cmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                Log.eLog("Uknown", "(ERROR): " + e.ToString());
                throw new Exception(e.Message);
            }

            finally
            {
               
                if (conn != null)
                    conn.Close();
            }
            

        }


        
       
        
    }
}
